package it.unibz.easytravel.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class TravellersDbAdapter {

	private static final String TAG = "TravellersDb";

	private static final String TABLE_NAME = "travellers";
	public static final String ID = "_id";
	public static final String TRAVEL_ID = "travel_id";
	public static final String PERSON_ID = "person_id";
	public static final String MONEY_FLOW = "money_flow";

	private DatabaseHelper mDbHelper;

	private static SQLiteDatabase mDb;

	private final Context mCtx;

	/**
	 * Database creation sql statement
	 */
	private static final String DATABASE_CREATE = 
		"create table " + TABLE_NAME + " ("
		+ ID + " integer primary key autoincrement, "
		+ TRAVEL_ID + " integer, "
		+ PERSON_ID + " integer,"
		+ MONEY_FLOW + " integer default 0);";

	public static void onCreate(SQLiteDatabase db) {
		db.execSQL(DATABASE_CREATE);
	}

	public static void onUpgrade(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		onCreate(db);
	}

	public TravellersDbAdapter(Context ctx) {
		this.mCtx = ctx;
	}

	public TravellersDbAdapter open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	public void cleanData(String travelId) {
		Log.i(TAG, "deleteTable");
		String where = TRAVEL_ID + "=" + travelId;
		ContentValues values = new ContentValues();
		values.put(MONEY_FLOW, 0);
		mDb.update(TABLE_NAME, values, where, null);
	}

	public static void removePerson(String travelId, String person_id) {
		Log.i(TAG, "deletePerson");
		String where = TRAVEL_ID + "=" + travelId + " and " + PERSON_ID + "="+person_id;
		mDb.delete(TABLE_NAME, where, null);
	}

	public static long insertTraveller(long travel_id, long person_id) {
		Log.i(TAG, "insertTraveller");
		ContentValues values = new ContentValues();
		values.put(TRAVEL_ID, travel_id);
		values.put(PERSON_ID, person_id);
		return mDb.insert(TABLE_NAME, null, values);
	}

	/**
	 * SQL = SELECT money_flow FROM travellers WHERE person_id=userId AND travel_id =
	 * travelId
	 * 
	 * @param userId
	 * @param travelId
	 * @return amount_flow
	 */
	public int getMoneyFlow(int userId, int travelId) {
		Log.i(TAG, "getMoneyFlow");
		String where = PERSON_ID + "=" + userId + " and " +TRAVEL_ID+"=" + travelId;
		Cursor cursor = mDb.query(TABLE_NAME, new String[] { MONEY_FLOW },
				where, null, null, null, null);
		if (!cursor.moveToFirst())
			throw new RuntimeException("user does not exist 4");
		return cursor.getInt(0);
	}

	/**
	 * SLQ = UPDATE moneyFlow SET money_flow = moneyFlow WHERE person_id = userId AND
	 * traveler_id = travelId
	 * 
	 * @param userId
	 * @param travelId
	 * @param moneyFlow
	 */
	public void setMoneyFlow(int userId, int travelId, int moneyFlow) {
		Log.i(TAG, "setMoneyFlow");
		String where = PERSON_ID + "=" + userId + " and " +TRAVEL_ID+"=" + travelId;
		ContentValues values = new ContentValues();
		values.put(MONEY_FLOW, moneyFlow);
		mDb.update(TABLE_NAME, values, where, null);
	}

	/**
	 * SQL1 = SELECT money_flow AS mf FROM moneyFlow WHERE person_id=userId AND
	 * travel_id = travelId SLQ2 = UPDATE moneyFlow SET money_flow =
	 * mf+moneyFlowToAdd WHERE person_id = userId AND traveler_id = travelId
	 * 
	 * @param userId
	 * @param travelId
	 * @param moneyFlowToAdd
	 */
	public void addToMoneyFlow(int userId, int travelId, int moneyFlowToAdd) {
		Log.i(TAG, "addToMoneyFlow(" + userId + "," + travelId + ","
				+ moneyFlowToAdd + ")");
		int moneyFlow = getMoneyFlow(userId, travelId);
		setMoneyFlow(userId, travelId, moneyFlow + moneyFlowToAdd);
	}

	/**
	 * SQL = SELECT * FROM travellers WHERE travel_id = travel_id
	 * 
	 * @return cursor to result list
	 */
	public Cursor getTravellers(int travel_id) {
		Log.i(TAG, "getTravellers");
		return mDb.query(TABLE_NAME, new String[] { ID, TRAVEL_ID, PERSON_ID, MONEY_FLOW }, TRAVEL_ID +"="+ travel_id, null, null, null, null);
	}

	public static boolean relationExists(int travelId, long person_id) {
		Log.i(TAG, "relationExists");
		String where = TRAVEL_ID+"='" + travelId+"' and "+PERSON_ID+"='" + person_id+"'";
		Cursor cursor = mDb.query(TABLE_NAME, new String[] { PERSON_ID }, where,
				null, null, null, null);
		if (cursor.moveToFirst()) {return true;}
		else {return false;}
	}

	public static void removeAllTravellers(int travelId) {
		Log.i(TAG, "removeAllTravellers");
		String where = TRAVEL_ID + "=" + travelId;
		mDb.delete(TABLE_NAME, where, null);
	}


}